CREATE TABLE [performance].[submit_sale](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[agent_code] [varchar](6) NOT NULL,
	[app_in_key] [varchar](30) NOT NULL,
	[certificate_num] [varchar](10) NOT NULL,
	[contract_num] [varchar](20) NOT NULL,
	[is_basic_plan] [varchar](1) NOT NULL,
	[last_update_date_time] [datetime2](7) NOT NULL,
	[last_update_user] [varchar](50) NULL,
	[lob] [varchar](3) NOT NULL,
	[plan_code] [varchar](20) NOT NULL,
	[plan_vers_num] [varchar](2) NULL,
	[sales_source] [varchar](30) NOT NULL,
	[sub_group_num] [varchar](10) NOT NULL,
	[submit_ape] [numeric](18, 2) NOT NULL,
	[submit_case_count] [numeric](3, 0) NOT NULL,
	[submit_date_time] [datetime2](7) NOT NULL,
	[submit_pc] [numeric](15, 0) NOT NULL,
	[submit_sales_key] [varchar](20) NOT NULL,
	[submit_sales_sub_key] [varchar](2) NOT NULL,
	[submit_type] [varchar](10) NOT NULL,
	[sys_code] [varchar](3) NOT NULL,
	[submit_sales_capture_key] varchar(20), 
	[agent_split_pct] numeric(7,4) not null, 
	[basic_plan_name_chi] varchar(60) not null,
    [basic_plan_code] varchar(20) not null, 
	[basic_plan_name_eng] varchar(60) not null,
	[basic_plan_vers_num] varchar(2) not null, 
	[coverage_plan_code] varchar(20) not null,
	[coverage_plan_name_chi] varchar(60) not null, 
	[coverage_plan_name_eng] varchar(60) not null,
	[coverage_plan_vers_num] varchar(2) not null, 
	[proposal_insured_name] varchar(100), 
	[proposal_owner_name] varchar(100),
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

CREATE TABLE [performance].[issue_sale](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[agent_code] [varchar](6) NOT NULL,
	[app_in_key] [varchar](30) NULL,
	[basic_plan_code] [varchar](20) NULL,
	[basic_plan_vers_num] [varchar](2) NULL,
	[certificate_num] [numeric](19, 2) NOT NULL,
	[contract_num] [varchar](20) NOT NULL,
	[core_indicator] [varchar](1) NOT NULL,
	[coverage_layer_plan_code] [varchar](20) NULL,
	[coverage_layer_type] [varchar](1) NULL,
	[coverage_layer_vers_num] [varchar](2) NULL,
	[dpr_date] [datetime2](7) NOT NULL,
	[is_adjustment_sales] [varchar](1) NOT NULL,
	[is_agent_own] [varchar](1) NOT NULL,
	[issue_case_count] [numeric](5, 2) NULL,
	[issue_pc] [numeric](9, 0) NULL,
	[issue_sales_key] [varchar](20) NOT NULL,
	[last_update_date_time] [datetime2](7) NOT NULL,
	[last_update_user] [varchar](50) NULL,
	[lob] [varchar](3) NOT NULL,
	[p_type] [varchar](2) NOT NULL,
	[policy_plan_type] [varchar](2) NOT NULL,
	[reason_code] [varchar](3) NOT NULL,
	[sub_group_num] [varchar](10) NOT NULL,
	[sys_code] [varchar](3) NOT NULL,
	[basic_plan_name_chi] varchar(60) not null,
    [basic_plan_name_eng] varchar(60) not null, 
	[coverage_plan_name_chi] varchar(60) not null, 
	[coverage_plan_name_eng] varchar(60) not null, 
	[policy_insured_name] varchar(100) not null,
	[policy_owner_name] varchar(100) not null,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

CREATE TABLE [performance].[sales_map](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[issue_sales_key] [varchar](20) NULL,
	[last_update_date_time] [datetime2](7) NOT NULL,
	[last_update_user] [varchar](50) NULL,
	[submit_issue_sales_map_key] [varchar](20) NOT NULL,
	[submit_sales_key] [varchar](20) NOT NULL,
	[submit_sales_sub_key] [varchar](2) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

create view [performance].[view_sales_record] as
	select m.submit_sales_key, m.submit_sales_sub_key, 
	p.agent_code, p.contract_num, p.sub_group_num, p.certificate_num, 
	p.lob, p.plan_code, 
	p.submit_date_time, p.submit_pc, p.submit_case_count, 
	d.dpr_date, d.issue_pc, d.issue_case_count
	from sales_map m
	left join submit_sale p on m.submit_sales_key = p.submit_sales_key and m.submit_sales_sub_key = p.submit_sales_sub_key
	left join issue_sale d on m.issue_sales_key = d.issue_sales_key
GO

ALTER AUTHORIZATION ON SCHEMA::performance TO dbo

GO